﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace DoAnASP
{
    public class DAO_NhanVien
    {
        //DTO_NhanVien nv = new DTO_NhanVien();

        // // khi nguoi dung dang ky thanh vien thi ma quyen = 3 la thanh viene // 
        // // ma quyen = 1 la  admin
        // // ma quyen = 2 laf nhan vien
        DAO_dataProvider kn = new DAO_dataProvider();
        public DataTable dtNhanVien = new DataTable();
        public string TenNhanVien = "";

        public void DangKyTV (DTO_NhanVien nv) // // them thanh vien
        {
            
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            DAO_NhanVien DAO_NV = new DAO_NhanVien();
            string sql = "insert into NguoiDung(MaQuyen,TenDangNhap,MatKhau,HoTen,Email,SoDT,DiaChi) values (@MaQuyen, @TenDangNhap, @MatKhau, @HoTen, @Email, @SoDT , @DiaChi )";
            SqlCommand cmd = new SqlCommand(sql,kn.conn);
            //cmd.Parameters.Add("@IDNguoiDung", SqlDbType.Int).Value = 7;
            cmd.Parameters.Add("@MaQuyen", SqlDbType.Int).Value = 1;
            cmd.Parameters.Add("@TenDangNhap", SqlDbType.VarChar).Value = nv.Username.ToString();
            cmd.Parameters.Add("@MatKhau", SqlDbType.VarChar).Value = nv.Password.ToString();
            cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar).Value = nv.FullName.ToString();
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = nv.Email.ToString();
            cmd.Parameters.Add("@SoDT", SqlDbType.VarChar).Value = nv.NumBerPhone.ToString();
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = nv.Address.ToString() ;
            
             
            // thực thi câu truy vấn
            cmd.ExecuteNonQuery();
            //cmd.Dispose();
            kn.Close_Connection();
            
        }
        // // khi admin them them nhan viên
        public void ThemNhanVien (DTO_NhanVien nv)
        {

            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql2 = "insert into NguoiDung(MaQuyen,TenDangNhap,Matkhau,Hoten,Email,SDT,Diachi) values ('" + nv.IDQuyenTruyCap + "','" + nv.Username + "','" + nv.Password + "','" + nv.FullName + "','" + nv.Email + "','" + nv.NumBerPhone + "','" + nv.Address + "')";
            SqlCommand cmd = new SqlCommand(sql2, kn.conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            kn.Close_Connection();

        }

        public string LayTenNhanVien(DTO_NhanVien nv)
        {
            kn.ChuoiKetNoi();
            kn.Open_Connection();

            String sql3 = "select * from NguoiDung where TenDangNhap = '" + nv.Username + "' ";
            SqlCommand cmd = new SqlCommand(sql3,kn.conn);
            SqlDataAdapter da = new SqlDataAdapter(sql3,kn.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string hoten = "";
            hoten = dt.Rows[0]["HoTen"].ToString();
            cmd.Dispose();
            kn.Close_Connection();
            return hoten;
        }

        // /// dung để thay đổi mật khẩu
        public DataTable LayTenQuyen (DTO_NhanVien nv)
        {
            kn.ChuoiKetNoi();
            kn.Open_Connection();

            String sql = "select Quyen.Ten, NguoiDung.HoTen, Quyen.MaQuyen from NguoiDung, Quyen where NguoiDung.TenDangNhap = '" + nv.Username + "' and NguoiDung.MaQuyen = Quyen.MaQuyen ";
            SqlCommand cmd = new SqlCommand(sql, kn.conn);
            SqlDataAdapter da = new SqlDataAdapter(sql, kn.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cmd.Dispose();
            kn.Close_Connection();
            return dt;
        }

        public DataTable LoadQuyen()
        {
            DataTable dt = new DataTable();
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql4 = "select * from Quyen";
            SqlCommand cmd = new SqlCommand(sql4,kn.conn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            kn.Close_Connection();
            return dt;
        }

        public bool KiemTraDN( DTO_NhanVien nv)
        {
            bool kt = false;

            kn.ChuoiKetNoi();
            kn.Open_Connection();
            DataTable dt = new DataTable();
            string sql = "Select * from NguoiDung  where TenDangNhap = '" + nv.Username + "' and MatKhau = '"+ nv.Password +"'";// =@matkhau @tendangnhap
            SqlCommand cmd = new SqlCommand(sql,kn.conn);

           // cmd.Parameters.Add("@tendangnhap", SqlDbType.VarChar).Value = nv.Username;
           // cmd.Parameters.Add("@matkhau", SqlDbType.VarChar).Value = nv.Password;
        
            SqlDataReader reader = cmd.ExecuteReader();
            dt.Load(reader);
            dtNhanVien.Load(reader);
       
            cmd.Dispose();
            reader.Dispose();
           int k = dt.Rows.Count;
           // k = cmd.ExecuteNonQuery();
           
           if (k > 0)
               kt = true;
           else
               kt = false;
            kn.Close_Connection();
            return kt;
  
        }

        public bool KiemTraTonTai(DTO_NhanVien nv)
        {
            bool kt = false;

            kn.ChuoiKetNoi();
            kn.Open_Connection();
            DataTable dt = new DataTable();
            string sql = "Select * from NguoiDung  where TenDangNhap = '" + nv.Username + "' ";// =@matkhau @tendangnhap
            SqlCommand cmd = new SqlCommand(sql, kn.conn);

            SqlDataReader reader = cmd.ExecuteReader();
            dt.Load(reader);
            
            cmd.Dispose();
            reader.Dispose();
            int k = dt.Rows.Count;
            // k = cmd.ExecuteNonQuery();
            if (k > 0)
                kt = true;
            else
                kt = false;
            kn.Close_Connection();
            return kt;

        }
        // // dung để lấy thông tin cá nhân để cập nhập
        public DataTable LoadCaNhan(DTO_NhanVien nv) // // // dung noi bo thoi
        {
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            DataTable dt = new DataTable();
            string sql = "Select * from NguoiDung  where TenDangNhap = '" + nv.Username + "'" ;
            SqlCommand cmd = new SqlCommand(sql,kn.conn);

            //SqlDataReader reader = cmd.ExecuteReader();

            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception)
            {
                
                throw;
            }

            //dt.Load(reader);
            
            //reader.Dispose();  
            kn.Close_Connection();
            return dt;
        }

        public bool CapNhatThongTin (DTO_NhanVien nv)
        {
            bool flag = false;
            kn.ChuoiKetNoi();
            kn.Open_Connection();
            string sql = "update NguoiDung set MatKhau = '" + nv.Password + "', HoTen = N'" + nv.FullName + "',  Email = '" + nv.Email + "', SoDT = '" + nv.NumBerPhone + "', DiaChi = N'" + nv.Address + "'  where IDNguoiDung = '" + nv.IDThanhVien + "' ";
           
            //string sql = "update NguoiDung  set HoTen = N'" + nv.FullName + "'  where IDNguoiDung = '" + nv.IDThanhVien + "' ";


            SqlCommand cmd = new SqlCommand(sql,kn.conn);
            try 
            {
                cmd.ExecuteNonQuery();
                flag = true ;
            }
            catch (Exception)
            {
		
                flag = false;
            }
            
            kn.Close_Connection();
            return flag;
        }

        /// <summary>
        /// ok roi
        /// </summary>
        /// <param name="nv"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public bool CapNhatMatKhau(DTO_NhanVien nv, string p)
        {
            bool flag = false;
            kn.ChuoiKetNoi();
            kn.Open_Connection();

            string sql = "update NguoiDung  set MatKhau = '" + nv.Password + "' where TenDangNhap = '" + nv.Username + "' and MatKhau = '"+ p +"'";
            SqlCommand cmd = new SqlCommand(sql, kn.conn);
            try
            {
                cmd.ExecuteNonQuery();
                flag = true;
            }
            catch (Exception)
            {

                flag = false;
            }

            kn.Close_Connection();
            return flag;
        }

        public void XoaNhanVien(DTO_NhanVien nv)
        {
            kn.ChuoiKetNoi();
            kn.Open_Connection();

            SqlCommand cmd = new SqlCommand("Delete from NguoiDu ng where IDNguoiDung = '"+ nv.IDThanhVien+ "' ", kn.conn);

            cmd.ExecuteNonQuery();

            kn.Close_Connection();
        }
            
    }
}
